package com.module.codeManage.setcode.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;

import base.dao.GenericHibernateDao;
import base.util.StringUtil;
import base.util.Tools;

import com.module.codeManage.setcode.common.SetCodeConstant;
import com.module.codeManage.setcode.vo.FirstCode;
import com.module.codeManage.setcode.vo.FirstCodeItem;
import com.mysql.jdbc.Statement;


public class FirstCodeDao extends GenericHibernateDao<FirstCode,Integer > implements SetCodeConstant{
	static FirstCodeDao dao;
	
	public FirstCodeDao(){
		
	}
	public synchronized static FirstCodeDao getInstance()
    {
        if (dao == null)
        {
            dao = new FirstCodeDao();
        }
        return dao;
    }
	/**
	 * 根据码号,查询第一层码信息
	 * @param session
	 * @param code
	 * @return
	 * @throws Exception
	 */
	public FirstCode selectInfoByCode(Session session,String code)throws Exception{
		FirstCode fc = new FirstCode();
		Query query = session.createQuery(" from  FirstCode as a where a.codeInfo='"+code+"'");
		List list=query.list();
		if(list!=null && !list.isEmpty()){
			 fc=(FirstCode)list.get(0);
		}
		return fc;
	}
	/**
	 * 查询所有未发货的卷号
	 * @return
	 * @author dengsq
	 */
	public List<String> findRollList(Session session,String orgId,String batchCodeId)throws Exception{
		StringBuffer hql = new StringBuffer("select distinct  rollNum from FirstCode ");
		if(!StringUtil.isNull(orgId)){
			hql.append("where i_state = "+STATE_OUT+" and orgId = '"+orgId+"' and rollNum <>''");
			if(Tools.isBlank(batchCodeId)){
				hql.append(" and batchCodeId is null");
			}
			
		}else
			//hql.append("where i_state <>"+STATE_OUT+"");
			hql.append("where i_state ="+STATE_IN+"");
		Query query = session.createQuery(hql.toString());
		return  query.list();
	}
	
	/**
	 * 获得某卷的码的状态
	 * @param session
	 * @param rollNum
	 * @return
	 * @throws SQLException
	 */
	private Integer getStateByRollNum(Session session,String rollNum){
		Query query=session.createQuery(" select  distinct(state) FROM FirstCode  where rollNum='"+rollNum+"'");
		List list=query.list();
		if(list != null && !list.isEmpty())
			return  (Integer)list.get(0);
		else
			return 0;
	}
	/**
	 * 验证标签的有效性
	 * @param firstCode
	 * @return
	 * @author dengsq
	 */
	public Map<String ,Object> checkTag(Session session,Map<String ,Object> tagmap)throws Exception{
		String msg = "0";
		boolean isTrue = true;
		int countCode = 0;//所有码的数量
		Map<String ,Object> resultmap = new HashMap<String, Object>();//返回结果集合
		FirstCode firstCode = (FirstCode) tagmap.get("firstCode");
		if(null != firstCode){
			Integer endNum =0 ;Integer starNum=0;
			List<Map<String ,Object>> list = new ArrayList<Map<String ,Object>>();
			boolean isState = false;
			if(firstCode.getState() != null)//是否出库状态
				isState = true;
			for(FirstCodeItem fci : firstCode.getItems()){
				//add panzs
				int state=this.getStateByRollNum(session, fci.getRollNum());
				boolean isFlag = (Boolean) tagmap.get("flag");
				if(!isFlag && state==3){//dengsq 添加标识bean.isFlag()如果为false表示出库验证否则为贴码验证。
					msg = fci.getRollNum()+"卷已经出库";
					isTrue = false;
					break;
				}
				//end
				starNum = this.getCodeNum(session,fci.getRollNum(),fci.getBeginCode(),isState);
				if(starNum == null || starNum==0){
					msg = "卷号【"+fci.getRollNum()+"】开始码【 "+fci.getBeginCode()+"】不存在!";
					isTrue = false;
					break;
				}
				endNum = this.getCodeNum(session,fci.getRollNum(),fci.getEndCode(),isState);
				if(endNum == null || endNum==0){
					msg = "卷号【"+fci.getRollNum()+"】结束码【 "+fci.getEndCode()+"】不存在!";
					isTrue = false;
					break;
				}
				if(starNum>endNum){
					msg = "卷号【"+fci.getRollNum()+"】，"+"开始码序号不能大于结束码序号";
					isTrue = false;
					break;
				}
				countCode += (endNum-starNum)+1;
				list.add(this.getTrueMsg(starNum, endNum, fci.getBeginCode(),
						fci.getEndCode(), fci.getRollNum()));
			}
			if(isTrue){
				resultmap.put("state", true);
				resultmap.put("items", list);

			}else
				resultmap = this.getFalseMsg(msg);
		}		
		return resultmap;
	}
	private Map<String ,Object>  getTrueMsg(int starNum,int endNum,
			String starCode,String endCode,String rollNum) {
		Map<String ,Object> map = new HashMap<String, Object>();
		map.put("startSeq",starNum);//码的开始序号
		map.put("endSeq",endNum);//码的结束序号
		map.put("volumeNo",rollNum);
		map.put("startVolumeCode",starCode);
		map.put("endVolumeCode",endCode);
		return map;
	}
	private Map<String ,Object> getFalseMsg(String msg){
		Map<String ,Object> map = new HashMap<String, Object>();
		map.put("state", false);
		map.put("msg", msg);
		return map;
	}
	/**
	 * 获取码的序号
	 * @param beginCode 码
	 * @param isState 是否是出库状态
	 * @return
	 * @author dengsq
	 */
	private Integer getCodeNum(Session session,String rollNum,String beginCode,boolean isState)throws Exception{
		StringBuffer starhql = new StringBuffer( "select sequenceNum  from FirstCode  where rollNum=? and  codeInfo=?");
		if(isState)//如果是出库 则码的状态不能为出库状态
			starhql.append(" and state <> "+STATE_OUT+"");
		else
			starhql.append(" and state = "+STATE_OUT+"");
		Query starQuery = session.createQuery(starhql.toString());
		starQuery.setParameter(0, rollNum);
		starQuery.setParameter(1, beginCode);
		List<Integer> list = starQuery.list();
		if(list != null && !list.isEmpty())
			return  list.get(0);
		else
			return 0;
	}
	/**
	 * 如果当前码已经贴码，update码的贴码ID
	 * @param session
	 * @param items
	 * @return
	 * @throws SQLException 
	 */
	public void updateTagBatchCode(Connection conn,List<FirstCodeItem> items) throws SQLException{
		boolean isFirst = true;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<FirstCode> firstCodeList = new ArrayList<FirstCode>();
		try{
			for(FirstCodeItem item:items){
				if(isFirst){
					String updateHql ="update code_firstcode set c_batch_code_id = null where c_batch_code_id = '"+item.getBatchCodeId()+"'"; 
					ps = conn.prepareStatement(updateHql,Statement.RETURN_GENERATED_KEYS);
					ps.executeUpdate();
					isFirst = false;
				}
				
				String selectHql ="select i_id from code_firstcode where c_roll_num = ? and i_sequence_num>=? and i_sequence_num<=?"; 
				ps = conn.prepareStatement(selectHql);
				 ps.setString(1,item.getRollNum());
				 ps.setInt(2, item.getStarNum());
				 ps.setInt(3, item.getEndNum());
				 rs = ps.executeQuery();
				 while(rs.next()){
					 FirstCode fc = new FirstCode();
					 fc.setId(rs.getInt(1));
					 fc.setBatchCodeId(item.getBatchCodeId());
					 firstCodeList.add(fc);
				 }
			}
			String sql=" update  code_firstcode set c_batch_code_id=? where i_id=? ";
			 ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			 int index =0;
			 for(FirstCode fc:firstCodeList){
				 ps.setString(1, fc.getBatchCodeId());
				 ps.setInt(2, fc.getId());
				 ps.addBatch();//将数据打包
				 if(index!=0&&index%500==0){
					 ps.executeBatch();//执行打包后的sql   
				 }
				 index++;
			 }
			 ps.executeBatch();
		}finally{
				 close(rs);
				 close(ps);
			
		}
		
	}
}
